﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Common;
using System.Text.RegularExpressions;

public partial class top_reviewnew_dbcheck : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sql = string.Empty;
        string tempStr = string.Empty;
        DataTable dt = new DataTable();         //原始表
        DataTable dtProc = new DataTable();     //存储过程列表
        DataTable dtIndex = new DataTable();    //索引列表
        DataTable tempDt = new DataTable();     //临时表，用于存放DB1-4的数据结构

        //获取全部表
        sql = "SELECT Name FROM TeteCrmSaasNew20121103..SysObjects Where XType='U' ORDER BY Name";
        dt = utils.ExecuteDataTable(sql);

        //获取全部存储过程
        sql = "SELECT Name FROM TeteCrmSaasNew20121103..SysObjects Where XType='P' ORDER BY Name";
        dtProc = utils.ExecuteDataTable(sql);

        //获取全部索引
        sql = @"select b.name from 
                TeteCrmSaasNew20121103.sys.tables a left join TeteCrmSaasNew20121103.sys.indexes b
                on a.object_id = b.object_id
                WHERE b.type_desc = 'NONCLUSTERED'
                AND a.name NOT LIKE '%Old%' AND a.name NOT LIKE '%1%' AND a.name NOT LIKE '%2%' AND a.name NOT LIKE '%3%' AND a.name NOT LIKE '%4%'";
        dtIndex = utils.ExecuteDataTable(sql);

        //循环比4张表
        for (int j = 1; j <= 4; j++)
        {
            Response.Write("【数据库DB-" +j.ToString() + "检查中...】<br>");
            //获取DB[J]的表
            sql = "SELECT Name FROM TeteCrmSaasDB" + j.ToString() + "..SysObjects Where XType='U' ORDER BY Name";
            tempDt = utils.ExecuteDataTable(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tempStr = dt.Rows[i][0].ToString();
                //查看其他库是否有此表，去掉带数字和old这些关键字的
                if (tempStr.IndexOf("Old") == -1 && tempStr.IndexOf("Bak") == -1 && tempStr.IndexOf("old") == -1 && !Regex.IsMatch(tempStr, @"([0-9])"))
                {
                    if (!IsInclude(tempDt, tempStr))
                    {
                        Response.Write("该表不存在-" + tempStr + "<br>");
                    }
                    else
                    { 
                        //比较里面的字段
                        CompareTable(tempStr, j);
                    }
                }
            }

            Response.Write("【数据库DB-" + j.ToString() + " 存储过程 检查中...】<br>");
            //获取DB[J]的表
            sql = "SELECT Name FROM TeteCrmSaasDB" + j.ToString() + "..SysObjects Where XType='P' ORDER BY Name";
            tempDt = utils.ExecuteDataTable(sql);

            for (int i = 0; i < dtProc.Rows.Count; i++)
            {
                tempStr = dtProc.Rows[i][0].ToString();
                //查看其他库是否有此表，去掉带数字和old这些关键字的
                if (!IsInclude(tempDt, tempStr))
                {
                    Response.Write("该存储过程不存在-" + tempStr + "<br>");
                }
            }


            Response.Write("【数据库DB-" + j.ToString() + " 索引 检查中...】<br>");
            //获取DB[J]的表
            //sql = "SELECT Name FROM TeteCrmSaasDB" + j.ToString() + "..SysObjects Where XType='P' ORDER BY Name";
            sql = @"select b.name from 
                TeteCrmSaasDB" + j.ToString() + @".sys.tables a left join TeteCrmSaasDB" + j.ToString() + @".sys.indexes b
                on a.object_id = b.object_id
                WHERE b.type_desc = 'NONCLUSTERED'
                AND a.name NOT LIKE '%Old%' AND a.name NOT LIKE '%1%' AND a.name NOT LIKE '%2%' AND a.name NOT LIKE '%3%' AND a.name NOT LIKE '%4%'";
            tempDt = utils.ExecuteDataTable(sql);

            for (int i = 0; i < dtIndex.Rows.Count; i++)
            {
                tempStr = dtIndex.Rows[i][0].ToString();
                //查看其他库是否有此表，去掉带数字和old这些关键字的
                if (!IsInclude(tempDt, tempStr))
                {
                    Response.Write("该索引不存在-" + tempStr + "<br>");
                }
            }

            Response.Write("<hr>");
        }
    }

    /// <summary>
    /// 数据库单表比较
    /// </summary>
    /// <param name="tempStr"></param>
    /// <param name="i"></param>
    private void CompareTable(string tableName, int index)
    {
        string sql = string.Empty;
        string tempStr = string.Empty;
        DataTable dt = new DataTable();         //原始表
        DataTable dtCol = new DataTable();      //该表全部字段

        //获取该表全部字段
        sql = @"select b.name, b.column_id, b.system_type_id,b.user_type_id from 
                TeteCrmSaasNew20121103.sys.tables a left join TeteCrmSaasNew20121103.sys.columns b
                ON a.object_id = b.object_id
                where a.name = '" + tableName + @"'";
        dt = utils.ExecuteDataTable(sql);

        //获取比较表全部字段
        sql = @"select b.name, b.column_id, b.system_type_id,b.user_type_id from 
                TeteCrmSaasDB" + index.ToString() + @".sys.tables a left join TeteCrmSaasDB" + index.ToString() + @".sys.columns b
                ON a.object_id = b.object_id
                where a.name = '" + tableName + @"'";
        dtCol = utils.ExecuteDataTable(sql);

        //开始比较
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            tempStr = dt.Rows[i][0].ToString();
            if (!IsInclude(dtCol, tempStr))
            {
                Response.Write("该表【" + tableName + "】该字段【" + tempStr + "】不存在<br>");
            }
        }
    }

    /// <summary>
    /// 判断是否包含表
    /// </summary>
    /// <param name="tempDt"></param>
    /// <param name="tempStr"></param>
    /// <returns></returns>
    private bool IsInclude(DataTable tempDt, string tempStr)
    {
        for (int i = 0; i < tempDt.Rows.Count; i++)
        {
            if (tempDt.Rows[i][0].ToString().Trim() == tempStr.Trim())
            {
                return true;
            }
        }

        return false;
    }
}